
package com.compuvest.environment

import javax.servlet.ServletConfig
import javax.servlet.ServletException

import javax.servlet.http.HttpServlet
import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

import javax.naming.InitialContext
import java.net.URLEncoder

import groovy.sql.Sql


class UpdateSitePages extends HttpServlet {

    def sql

// <editor-fold defaultstate="collapsed" desc="init()">
    def void init(ServletConfig config) {
	super.init(config)

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)

        sql.eachRow('select VERSION()') {
            println ">>>>init UpdateSitePages() sql version=${it[0]}"
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="processRequest()">
    def void processRequest(request, response) {

        def time1 = refreshBrandPage()
        def time2 = refreshLeftMenuJSON()
        def time3 = refreshCountry()
        def time4 = refreshAllState()
        def time5 = refreshUsaState()
        def time6 = refreshItemsCategoryPage()
        def time7 = refreshItemsSubCategoryPage()
        def time8 = refreshItemsFirstPage()
        def time9 = refreshItemsSearchPage();

        def xml = """
            <?xml version='1.0' encoding='UTF-8'?>
            <status>
             <brand_page>${time1}ms.</brand_page>
             <menu_json>${time2}ms.</menu_json>
             <country>${time3}ms.</country>
             <allstate>${time4}ms.</allstate>
             <usastate>${time5}ms.</usastate>
             <categorypage>${time6.time}ms.[${time6.items}]</categorypage>
             <subcatpage>${time7}ms.</subcatpage>
             <firstpage>${time8}ms.</firstpage>
             <searchpage>${time9}ms.</searchpage>
            </status>"""

        response.setContentType("text/xml;charset=UTF-8")
        def out = response.getWriter()
        try {
            out.print(xml.toString())
        }
        finally {
            out.close()
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshBrandPage()">
    def final static BRAND_PAGE_MFRNAME =
        "SELECT LEFT(mfr_name,1) mfrChar,COUNT(DISTINCT(mfr_name)) cnt " +
        "FROM sklad.items " +
        "WHERE active='Y' " +
        "GROUP BY LEFT(mfr_name,1)"

    def final static BRAND_PAGE_MFRLINK =
        "SELECT mfr_name FROM sklad.items " +
        "WHERE active='Y' AND LEFT(SUBSTRING_INDEX(mfr_name,'/',-1),1)=? OR LEFT(mfr_name,1)=? " +
        "GROUP BY mfr_name"

    def final static UPDATE_BRAND_PAGE = "UPDATE sklad.environment SET content=? WHERE name='brandpage'"

    def refreshBrandPage() {

        def startEvent = System.currentTimeMillis()

        def brand = new StringBuilder()
	brand << "<div style='padding:11px 10px ;text-align:left;margin-top:20px;'>"

        def total = 0
        def mfrList = new ArrayList<String>()
        def rmfrChar
        sql.eachRow(BRAND_PAGE_MFRNAME)
        { row ->

            rmfrChar = row.mfrChar
            brand << " <a id='hlk' href='#${rmfrChar}'>${rmfrChar}</a>&nbsp;"

            mfrList.add(rmfrChar)

            total += row.cnt
        }

        total = ((total + 27) / 3).toInteger()

        def column = 1, pos = 0
        brand << "<br/><br/><div style='background:white;'><fieldset>"
        brand << "<table style='font:normal 10px arial;'><tr valign='top'><td>"

        for(mfrChar in mfrList) {
            brand << "<p/>&nbsp;&nbsp;&nbsp;<a id='xln' name='${mfrChar}'>${mfrChar}</a>"

            sql.eachRow(BRAND_PAGE_MFRLINK, [mfrChar, mfrChar])
            { row ->

                brand << "<br/><b>&#x95;</b> <a class='lnk' href='ItemsBrand.jsp?brn=${URLEncoder.encode(row.mfr_name, "UTF-8")}'>${row.mfr_name}</a>"

                if(pos++ == (total * column + column)) {
                    brand << "</td><td>"
                    column++
                }
            }
        }
        brand << "</td></tr></table>"
        brand << "</fieldset></div>"
        brand << "</div>"

        sql.executeUpdate(UPDATE_BRAND_PAGE, [brand.toString()])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshLeftMenuJSON()">
    def final static MENU_JSON_CATEGORY =
        "SELECT category_id,CAST(CONCAT(" +
        "'{id:',category_id," +
        "',nm:',QUOTE(name)," +
        "',s:[') AS CHAR)  gcat " +
        "FROM sklad.category " +
        "WHERE publish='Y' AND parent=0 " +
        "ORDER BY name"

    def final static MENU_JSON_SUBCATEGORY =
        "SELECT CAST(IFNULL(GROUP_CONCAT(CONCAT(" +
        "'{id:',category_id," +
        "',nm:',QUOTE(name)," +
        "'}')),'') AS CHAR) scat " +
        "FROM sklad.category " +
        "WHERE publish='Y' AND parent=? " +
        "ORDER BY name"

    def final static UPDATE_LEFTMENU = "UPDATE sklad.environment SET content=? WHERE name='leftmenu'"

    def refreshLeftMenuJSON() {

        def startEvent = System.currentTimeMillis()

        def menu = new StringBuilder()
        menu << '['

        def idx = 0
        sql.eachRow(MENU_JSON_CATEGORY)
        { row ->

            if(idx++) menu << ','
            menu << "${row.gcat}"

            menu << sql.firstRow(MENU_JSON_SUBCATEGORY, [row.category_id]).scat

            menu << ']}'
        }
        menu << ']'

        sql.executeUpdate(UPDATE_LEFTMENU, [menu.toString()])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshCountry()">
    def final static SELECT_COUNTRY = "SELECT countryID,description FROM sklad.uspszone ORDER BY description"
    def final static UPDATE_COUNTRY = "UPDATE sklad.environment SET content=? WHERE name='country'"

    def refreshCountry() {

        def startEvent = System.currentTimeMillis()

        def country = new StringBuilder()
        sql.eachRow(SELECT_COUNTRY)
        { row ->
            country << "<option value='${row.countryID}'>${row.description}</option>"
        }

        sql.executeUpdate(UPDATE_COUNTRY, [country.toString()])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshAllState()">
    def final static SELECT_ALLSTATE = "SELECT state_code,description FROM sklad.state_code"
    def final static UPDATE_ALLSTATE = "UPDATE sklad.environment SET content=? WHERE name='allstate'"

    def refreshAllState() {

        def startEvent = System.currentTimeMillis()

        def state = new StringBuilder()
        sql.eachRow(SELECT_ALLSTATE)
        { row ->
            state << "<option value='${row.state_code}'>${row.description}</option>"
        }

        sql.executeUpdate(UPDATE_ALLSTATE, [state.toString()])

	return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshUsaState()">
    def final static SELECT_USASTATE    = "SELECT state_code,description,LCASE(SUBSTRING_INDEX(description,' ',-1)) stname FROM sklad.state_code WHERE usa=1"
    def final static UPDATE_USASTATE    = "UPDATE sklad.environment SET content=? WHERE name='usastate'"
    def final static UPDATE_USASTATEMAP = "UPDATE sklad.environment SET content=? WHERE name='usastatemap'"

    def refreshUsaState() {

        def startEvent = System.currentTimeMillis()

        def state = new StringBuilder()
        def statemap = [:]
        sql.eachRow(SELECT_USASTATE)
        { row ->
            state << "<option value='${row.state_code}'>${row.description}</option>"
            statemap[row.stname] = row.state_code
        }

        sql.executeUpdate(UPDATE_USASTATE, [state.toString()])
        sql.executeUpdate(UPDATE_USASTATEMAP, [statemap])

	return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshItemsCategoryPage()">
// items Category to Map
    def final static SELECT_ITEMS_CATEGORY_PAGE = "SELECT CAST(category_id AS CHAR) catid FROM sklad.category WHERE publish='Y' AND parent=0"
    def final static SELECT_SUBITEMS_CATEGORY_PAGE =
        "SELECT CAST(CONCAT('[',GROUP_CONCAT(IFNULL(CONCAT(" +
        "'{id:',it.item_id," +
        "',name:',QUOTE(it.name)," +
        "',price:',it.sell_price," +
        "',sku:',QUOTE(it.accwareCatID)," +
        "'}'),'{}')),']') AS CHAR) json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE it.active='Y' AND c.parent=? AND im.categorypage=1 " +
        "LIMIT 30"
    def final static UPDATE_ITEMS_CATEGORY_PAGE = "UPDATE sklad.environment SET content=? WHERE name='itemscategory'"

    def refreshItemsCategoryPage() {

        def startEvent = System.currentTimeMillis()

        def hashmap = [:]
        def state = new StringBuilder()
        sql.eachRow(SELECT_ITEMS_CATEGORY_PAGE)
        { row ->

            hashmap[row.catid] = sql.firstRow(SELECT_SUBITEMS_CATEGORY_PAGE, [row.catid]).json
        }

        sql.executeUpdate(UPDATE_ITEMS_CATEGORY_PAGE, [hashmap])

        return [time: System.currentTimeMillis() - startEvent, items: hashmap]
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshItemsSubCategoryPage()">
// advert items to Category page
    def final static SELECT_ITEMS_SUBCATEGORY_PAGE =
        "SELECT CAST(c.category_id AS CHAR) categoryID " +
        "FROM sklad.category c " +
        "LEFT JOIN sklad.category g ON c.parent=g.category_id " +
        "WHERE g.parent=0 AND c.publish='Y' AND g.publish='Y' " +
        "ORDER BY c.parent"
    def final static SELECT_SUBITEMS_SUBCATEGORY_PAGE =
        "SELECT CAST(CONCAT('[',GROUP_CONCAT(IFNULL(CONCAT(" +
        "'{id:',it.item_id," +
        "',name:',QUOTE(it.name)," +
        "',price:',it.sell_price," +
        "',sku:',QUOTE(it.accwareCatID)," +
        "'}'),'{}')),']') AS CHAR) json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsMask im USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE it.active='Y' AND c.category_id=? AND im.bestprice=1 " + // fileld bestprice = subcategory page
        "LIMIT 15"
    def final static UPDATE_ITEMS_SUBCATEGORY_PAGE = "UPDATE sklad.environment SET content=? WHERE name='itemssubcat'"

    def refreshItemsSubCategoryPage() {

        def startEvent = System.currentTimeMillis()

        def hashmap = [:]
        sql.eachRow(SELECT_ITEMS_SUBCATEGORY_PAGE)
        { row ->

            hashmap[row.categoryID] = sql.firstRow(SELECT_SUBITEMS_SUBCATEGORY_PAGE, [row.categoryID]).json
        }

        sql.executeUpdate(UPDATE_ITEMS_SUBCATEGORY_PAGE, [hashmap])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshItemsFirstPage()">
// advert first page and WeeklySpesial
    def final static UPDATE_ITEMS_FIRST_PAGE = "UPDATE sklad.environment SET content=? WHERE name='itemsfirstpage'"

    def refreshItemsFirstPage() {

        def startEvent = System.currentTimeMillis()

        def hashmap = [:]
        ['573': '573', '446': '446', '9': '9', '30': '30', '304': '304', '13': '13', '397': '397', '625': '625,474,585'].each { catid, catstr ->
        
            hashmap[catid] = sql.firstRow(
                "SELECT CAST(CONCAT('[',GROUP_CONCAT(IFNULL(CONCAT(" +
                "'{id:',it.item_id," +
                "',name:',QUOTE(it.name)," +
                "',price:',it.sell_price," +
                "',sku:',QUOTE(it.accwareCatID)," +
                "'}'),'{}')),']') AS CHAR) json " +
                "FROM sklad.items it " +
                "LEFT JOIN sklad.itemsMask im USING(item_id) " +
                "LEFT JOIN sklad.category c USING(category_id) " +
                "WHERE it.active='Y' AND im.firstpage=1 AND c.parent IN (${catstr}) " +
                "LIMIT 30").json
        }

        sql.executeUpdate(UPDATE_ITEMS_FIRST_PAGE, [hashmap])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="refreshItemsSearchPage()">
// advert Search page
    def final static SELECT_ITEMS_SEARCH_PAGE =
        "SELECT CAST(CONCAT('[',GROUP_CONCAT(IFNULL(CONCAT(" +
        "'{id:',it.item_id," +
        "',name:',QUOTE(it.name)," +
        "',price:',it.sell_price," +
        "',sku:',QUOTE(it.accwareCatID)," +
        "'}'),'{}')),']') AS CHAR) json " +
        "FROM sklad.items it " +
        "LEFT JOIN sklad.itemsAdvert ia USING(item_id) " +
        "LEFT JOIN sklad.category c USING(category_id) " +
        "WHERE it.active='Y' AND ia.banner_search=1 " +
        "LIMIT 15"
    def final static UPDATE_ITEMS_SEARCH_PAGE = "UPDATE sklad.environment SET content=? WHERE name='itemssearchpage'"

    def refreshItemsSearchPage() {

        def startEvent = System.currentTimeMillis()

        def json = sql.firstRow(SELECT_ITEMS_SEARCH_PAGE).json

        sql.executeUpdate(UPDATE_ITEMS_SEARCH_PAGE, [json])

        return System.currentTimeMillis() - startEvent
    }
// </editor-fold>


// <editor-fold defaultstate="collapsed" desc="refreshItemsSearchPage()">
    def void doGet(HttpServletRequest request, HttpServletResponse response) {
        processRequest(request, response)
    }
    def void doPost(HttpServletRequest request, HttpServletResponse response) {
        processRequest(request, response)
    }
    String getServletInfo() {
        return "UpdateSitePages"
    }
    def void destroy() {
        sql = null
    }
// </editor-fold>

}
